package fr.cephb.sql;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;





/**
 * @author lindenb
 *
 * various static Methods for SQL
 */
public class SQLUtilities
    {
    /**
     * escape a SQL string
     * @param s a word to convert
     * @return the converted string
     */
    static public String escape(CharSequence s)
        {
    	if(s==null) return null;
        int needed=-1;
        for(int i=0;i< s.length();++i)
	        {
	        switch(s.charAt(i))
	            {
	            case('\"'): 
	            case('\''): 
	            case('\n'): 
	            case('\t'):
	            case('\\'): needed=i; break;
	            default: break;
	            }
	        if(needed!=-1) break;
	        }
        if(needed==-1) return s.toString();
        
        StringBuffer buffer=new StringBuffer(s.subSequence(0,needed));
        
        for(int i=needed;i< s.length();++i)
            {
            switch(s.charAt(i))
                {
                case('\"'): buffer.append("\\\"");break;
                case('\''): buffer.append("\\\'");break;
                case('\n'): buffer.append("\\n");break;
                case('\t'): buffer.append("\\t");break;
                case('\\'): buffer.append("\\\\");break;
                default: buffer.append(s.charAt(i)); break;
                }
            }
        return buffer.toString();
        }



    

    
    /** put the current row into an array */
	static public Object[] toArray(ResultSet row) throws SQLException
    	{
    	Object array[]= new Object[row.getMetaData().getColumnCount()];
    	for(int i=0;i<array.length;++i)
    		array[i]=row.getObject(i+1);
    	return array;
    	}
    
    /** put first column of a ResultSet in a Vector */
	static public List<Object> resultSetToVector(ResultSet row) throws SQLException
    	{
		ArrayList<Object> data=new ArrayList<Object>(row.getMetaData().getColumnCount());
    	while(row.next()) data.add(row.getObject(1));
    	return data;
    	}
    
    /** put first column of a ResultSet in a generic Vector */
	static public <X> List<X> resultSetToVector(ResultSet row,Class<X> clazz) throws SQLException
    	{
    	ArrayList<X> data=new ArrayList<X>(row.getMetaData().getColumnCount());
    	while(row.next()) data.add(clazz.cast(row.getObject(1)));
    	return data;
    	}
   
    
    
    public static Number selectLastInsertId(Connection con) throws SQLException
    	{
    	PreparedStatement stmt=con.prepareStatement("select last_insert_id()");
    	Number n= selectOneValue(stmt.executeQuery(),Number.class);
    	stmt.close();
    	return n;
    	}
    
    public static Object selectOneValue(ResultSet row) throws SQLException
		{
	    boolean found=false;
	    Object value=null;
		while(row.next())
			{
			if(found) throw new SQLException("Excpected only one value");
			value=row.getObject(1);
			found=true;
			}
		if(!found) throw new SQLException("Excpected one value");
		return value;
		}
    
    public static <X> X selectOneValue(ResultSet row,Class<X> clazz) throws SQLException
    	{
    	return clazz.cast(selectOneValue(row));
    	}
    
    
    public static Object selectOneOrZeroValue(ResultSet row) throws SQLException
		{
	    boolean found=false;
	    Object value=null;
		while(row.next())
			{
			if(found) throw new SQLException("Excpected only one value");
			value=row.getObject(1);
			found=true;
			}
		return value;
		}
    
    public static <X> X selectOneOrZeroValue(ResultSet row,Class<X> clazz) throws SQLException
		{
		return clazz.cast(selectOneOrZeroValue(row));
		}
    
    public static void safeRollBack(Connection con)
    	{
    	if(con==null) return;
    	try {
			con.rollback();
			} 
    	catch (SQLException e)
    		{
			}
    	}
    public static void safeSetAutoCommit(Connection con,boolean autocommit)
		{
		if(con==null) return;
		try {
			con.setAutoCommit(autocommit);
			} 
		catch (SQLException e)
			{
			}
		}
    
    /** test if a connection is closed without throwing an exception
     * . return true if con is null or something bad happened */
    public static boolean safeIsClosed(Connection con)
		{
		if(con==null) return true;
		try {
			return con.isClosed();
			} 
		catch (SQLException e)
			{
			return true;
			}
		}
    
    /**  closes a connection without throwing an exception
     * @param con the connection to close . can be null , open or already closed
      */
    public static void safeClose(Connection con)
		{
		if(con==null || safeIsClosed(con)) return;
		try {
			con.close();
			} 
		catch (SQLException e)
			{
			//silent
			}
		}
    
    }
